<?php
include_once("ConnectionManager.php");

function authenticateUser($username, $password, $type){
	$query = "SELECT USER_USERNAME FROM MITI_USER WHERE USER_STATUS = 1 AND USER_TYPE = $type AND USER_USERNAME = '".htmlspecialchars($username, ENT_QUOTES)."' and USER_PASSWORD = '".htmlspecialchars($password, ENT_QUOTES)."'";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
	return ConnectionManager::getInstance()->getNumRows() > 0;
}

function authenticateClient($email, $password, $type){
	$query = "SELECT USER_ID, USER_USERNAME FROM MITI_USER WHERE USER_STATUS = 1 AND USER_TYPE = $type AND USER_EMAIL = '".htmlspecialchars($email, ENT_QUOTES)."' and USER_PASSWORD = '".htmlspecialchars($password, ENT_QUOTES)."'";
	ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
	return ConnectionManager::getInstance()->getNumRows() > 0;
}

function findUsernameBy($email){
	$query = "SELECT * FROM MITI_USER WHERE USER_EMAIL = '".htmlspecialchars($email, ENT_QUOTES)."'";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function findUserBy($username){
	$query = "SELECT * FROM MITI_USER WHERE USER_USERNAME = '".htmlspecialchars($username, ENT_QUOTES)."'";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function findUserById($userId){
	$query = "SELECT * FROM MITI_USER WHERE USER_ID = $userId";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function findUsersToExport(){
	$query = "SELECT USER_ID, USER_FULLNAME, (CASE USER_GENDER WHEN 1 THEN 'Nam' ELSE 'Nữ' END) AS 'USER_GENDER', USER_EMAIL, 
				     DATE_FORMAT(USER_DOB, '%d/%m/%Y'), USER_ADDRESS, USER_USERNAME, (CASE USER_STATUS WHEN 1 THEN 'Hoạt động' ELSE 'Bị khóa' END) AS 'STATUS', USER_STATUS, USER_PHONENUMBER
			  FROM MITI_USER WHERE USER_TYPE = 0";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function createUser($fullName, $gender, $email, $dob, $address, $username, $password, $phoneNumber){
	$dobValue = "";
	if($dob != "") $dobValue = "str_to_date('$dob', '%d/%m/%Y')";
	else $dobValue = "null";
	$query = "INSERT INTO MITI_USER(USER_FULLNAME, USER_GENDER, USER_EMAIL, USER_DOB, USER_ADDRESS, USER_USERNAME, USER_PASSWORD, USER_TYPE, USER_STATUS, USER_PHONENUMBER) VALUES('$fullName', $gender, '$email', $dobValue, '$address', '$username', '$password', 0, 1, '$phoneNumber')";
	ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
	return mysql_insert_id();
}

function isUniqueEmail($email){
    $query = "SELECT COUNT(*) AS COUNT FROM MITI_USER WHERE USER_EMAIL = '$email'";
    ConnectionManager::getInstance()->setQuery($query);
    $row = ConnectionManager::getInstance()->fetch(ConnectionManager::getInstance()->query());
    
    return $row["COUNT"];
}

function isUniqueUsername($username){
    $query = "SELECT COUNT(*) AS COUNT FROM MITI_USER WHERE USER_USERNAME = '$username'";
    ConnectionManager::getInstance()->setQuery($query);
    $row = ConnectionManager::getInstance()->fetch(ConnectionManager::getInstance()->query());
    
    return $row["COUNT"];
}

function editProfile($userId, $fullName, $address, $gender){
	$query = "UPDATE MITI_USER SET USER_FULLNAME = '$fullName', USER_ADDRESS = '$address', USER_GENDER = $gender WHERE USER_ID = $userId";
	ConnectionManager::getInstance()->setQuery($query);
	ConnectionManager::getInstance()->query();
	return mysql_affected_rows() > 0;
}

function changePassword($userId, $password){
	$query = "UPDATE MITI_USER SET USER_PASSWORD = '$password' WHERE USER_ID = $userId";
	ConnectionManager::getInstance()->setQuery($query);
	ConnectionManager::getInstance()->query();
	return mysql_affected_rows() > 0;
}

function udpateUserStatus($userId, $status){
	$query = "UPDATE MITI_USER SET USER_STATUS = $status WHERE USER_ID = $userId";
	ConnectionManager::getInstance()->setQuery($query);
	ConnectionManager::getInstance()->query();
	return mysql_affected_rows() > 0;
}
/*************************************************************************************************************************************************/
function getAllUserGroup(){
    $query = "SELECT * FROM MITI_ROLE";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();   
}

function getAllAvailableGroup($user_id){
    $query = "SELECT * FROM MITI_ROLE WHERE ROLE_ID NOT IN (SELECT ROLE_ID FROM MITI_USER_IN_ROLE WHERE USER_ID = $user_id)";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function getAllPrivilege(){
    $query = "SELECT * FROM MITI_PRIVILEGE";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function getAllPrivilegeOfUserGroup($usergroup_id){
    $query = "SELECT PRI_ID,(SELECT PRI_NAME FROM MITI_PRIVILEGE WHERE PRI_ID = RP.PRI_ID) AS PRI_NAME FROM MITI_ROLE_PRIVILEGE RP WHERE ROLE_ID = $usergroup_id";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function getAvailablePrivilegeOfUserGroup($usergroup_id){
    $query = "SELECT * FROM MITI_PRIVILEGE WHERE PRI_ID NOT IN (SELECT PRI_ID FROM MITI_ROLE_PRIVILEGE WHERE ROLE_ID = $usergroup_id)";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function createGroupDetail($usergrouptitle){
    $query = "INSERT INTO MITI_ROLE(ROLE_NAME) VALUES('$usergrouptitle')";
	ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
	return mysql_insert_id();
}

function hasUser($usergroup_id){
    $query = "SELECT COUNT(*) AS COUNT FROM MITI_USER_IN_ROLE WHERE ROLE_ID = $usergroup_id";
    ConnectionManager::getInstance()->setQuery($query);
    $row = ConnectionManager::getInstance()->fetch(ConnectionManager::getInstance()->query());
    return $row["COUNT"];
}

function getGroupDetail($usergroup_id){
    $query = "SELECT * FROM MITI_ROLE WHERE ROLE_ID = $usergroup_id";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return mysql_fetch_array($result);
}

function updateGroupDetail($usergroupid, $usergrouptitle){
    $query = "UPDATE MITI_ROLE SET ROLE_NAME = '$usergrouptitle' WHERE ROLE_ID = $$usergroupid";
	ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
	return $result;
}

function deleteUserGroup($usergroup_id){
    $query1 = "DELETE FROM MITI_ROLE_PRIVILEGE WHERE ROLE_ID = $usergroup_id";
    $query2 = "DELETE FROM MITI_ROLE WHERE ROLE_ID = $usergroup_id";
    ConnectionManager::getInstance()->setQuery($query1);
    ConnectionManager::getInstance()->query();
    ConnectionManager::getInstance()->setQuery($query2);
    ConnectionManager::getInstance()->query();
}

function assignPermissionOfUserGroup($usergroupid, $privileges){
    $query = "";
    for($i = 0; $i < count($privileges); $i++){
        $query = "INSERT INTO MITI_ROLE_PRIVILEGE VALUES(".$usergroupid.",".$privileges[$i].")";
        ConnectionManager::getInstance()->setQuery($query);
        ConnectionManager::getInstance()->query();        
    }
}

function unassignAllPermissionOfUserGroup($usergroupid){
    $query = "DELETE FROM MITI_ROLE_PRIVILEGE WHERE ROLE_ID = $usergroupid";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function updateAdminPassword($userId, $newPassword){
    $query = "UPDATE MITI_USER SET USER_PASSWORD = '$newPassword' WHERE USER_ID = $userId AND USER_TYPE = 1"; 
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return mysql_affected_rows() > 0;
}

/**************************************************************************************************************************************************/
function createStaff($fullName, $gender, $email, $username, $password){
	$query = "INSERT INTO MITI_USER(USER_FULLNAME, USER_GENDER, USER_EMAIL, USER_USERNAME, USER_PASSWORD, USER_TYPE, USER_STATUS) VALUES('$fullName', $gender, '$email', '$username', '$password', 1, 1)";
	ConnectionManager::getInstance()->setQuery($query);
    if (ConnectionManager::getInstance()->query()){
        return mysql_insert_id();    
    }else{
        return 0;
    }
}

function updateStaff($fullName, $gender, $email, $userid){
    $query = "UPDATE MITI_USER SET USER_FULLNAME='$fullName', USER_GENDER=$gender, USER_EMAIL='$email' WHERE USER_ID = $userid";
	ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function resetPassword($userid){
    $query = "UPDATE MITI_USER SET USER_PASSWORD = '".md5("123")."' WHERE USER_ID = $userid";
	ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
	return $result;
}

function assignStaffToGroup($userId, $groupid){
	$query = "INSERT INTO MITI_USER_IN_ROLE VALUES(".$userId.",".$groupid.")";
	ConnectionManager::getInstance()->setQuery($query);
	ConnectionManager::getInstance()->query();
}

function unassignStaffFromGroup($userid){
    $query = "DELETE FROM MITI_USER_IN_ROLE WHERE USER_ID = $userid";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return $result;
}

function getStaffByUsername($name){
	$query = "SELECT * FROM MITI_USER WHERE USER_TYPE = 1 AND USER_USERNAME = '$name'";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();

    if(isset($result)){
        return mysql_fetch_array($result);    
    }else{
        return null;
    }
	
}

function getStaffUser($user_id){
    $query = "SELECT * FROM MITI_USER WHERE USER_TYPE = 1 AND USER_ID = $user_id";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    if(isset($result)){
        return mysql_fetch_array($result);    
    }else{
        return null;
    }
}

function enableStaff($userid){
    $query = "UPDATE MITI_USER SET USER_STATUS = 1 WHERE USER_ID = $userid";
	ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
	return $result;
}

function disableStaff($userid){
    $query = "UPDATE MITI_USER SET USER_STATUS = 0 WHERE USER_ID = $userid";
	ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
	return $result;
}

function getAllStaffUser(){
    $query = "SELECT * FROM MITI_USER WHERE USER_TYPE = 1";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function getAllGroupOfUser($user_id){
    $query = "SELECT * FROM MITI_ROLE WHERE ROLE_ID IN (SELECT ROLE_ID FROM MITI_USER_IN_ROLE WHERE USER_ID = $user_id)";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function getPermissionOfUser($user_id){
    $query = "SELECT DISTINCT * FROM MITI_PRIVILEGE WHERE PRI_ID IN (SELECT PRI_ID FROM MITI_ROLE_PRIVILEGE WHERE ROLE_ID IN (SELECT ROLE_ID FROM MITI_ROLE WHERE ROLE_ID IN (SELECT ROLE_ID FROM MITI_USER_IN_ROLE WHERE USER_ID = $user_id)))
";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function doesStaffHasPermission($permission){
    $query = "";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}


?>